package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.beans.Cliente;
import model.ConnectionFactory;

/**
*
* @author Juliane
*/
public class ClienteDAO {

    private Connection con;
    private PreparedStatement stmtInserir;
    private PreparedStatement stmtAtualizar;
    private PreparedStatement stmtDeletar;    
    private PreparedStatement stmtListar;
    private PreparedStatement stmtBuscar;
    private PreparedStatement stmtBuscaId;
    private PreparedStatement stmtConsultas;

    public ClienteDAO() throws Exception {
        con = ConnectionFactory.getConnection();   
    }

    public ClienteDAO(Connection con) throws Exception {
        this.con = con;
    }

    public void inserirCliente(Cliente cliente) throws Exception {
        stmtInserir = con.prepareStatement("INSERT INTO cliente(nome, cpf, telefone, celular, email, endereco, dataNasc, sexo) VALUES(?,?,?,?,?,?,?,?)");
        stmtInserir.setString(1, cliente.getNome());
        stmtInserir.setString(2, cliente.getCpf());
        stmtInserir.setString(3, cliente.getTelefone());
        stmtInserir.setString(4, cliente.getCelular());
        stmtInserir.setString(5, cliente.getEmail());
        stmtInserir.setString(6, cliente.getEndereco());
        stmtInserir.setDate(7, new java.sql.Date(cliente.getDataNasc().getTime()));
        stmtInserir.setString(8, cliente.getSexo());
        stmtInserir.executeUpdate();
    }
    
    public void atualizarCliente(Cliente cliente) throws Exception{
        stmtAtualizar = con.prepareStatement("UPDATE cliente SET nome = ?, cpf = ?, telefone = ?, celular = ?, email = ?, endereco = ?, dataNasc = ?, sexo = ? WHERE idCliente = ?");
    	stmtAtualizar.setString(1, cliente.getNome());
        stmtAtualizar.setString(2, cliente.getCpf());
        stmtAtualizar.setString(3, cliente.getTelefone());
        stmtAtualizar.setString(4, cliente.getCelular());
        stmtAtualizar.setString(5, cliente.getEmail());
        stmtAtualizar.setString(6, cliente.getEndereco());
        stmtAtualizar.setDate(7, new java.sql.Date(cliente.getDataNasc().getTime()));
        stmtAtualizar.setString(8, cliente.getSexo());
        stmtAtualizar.setInt(9, cliente.getId());
    	stmtAtualizar.executeUpdate();
    }
    
    public void deletarCliente(int idCliente) throws Exception{
        stmtDeletar = con.prepareStatement ("DELETE FROM cliente WHERE idCliente = ?");
        stmtDeletar.setInt(1, idCliente);
    	stmtDeletar.executeUpdate();
    }

    public List<Cliente> listarCliente() throws Exception {
       stmtListar = con.prepareStatement("SELECT idCliente, nome FROM cliente");
       ResultSet rs = stmtListar.executeQuery();
       List<Cliente> listaCliente = new ArrayList();
       while(rs.next()){
          Cliente cliente = new Cliente(rs.getInt("idCliente"),rs.getString("nome"));
          listaCliente.add(cliente);
       }
       return listaCliente;
    }
    
    public List<Cliente> buscarCliente(String a) throws Exception{
        stmtBuscar = con.prepareStatement("SELECT * FROM cliente where nome like ? or cpf like ? or email like ?");
        String str = "%" + a + "%";
        stmtBuscar.setString(1, str);
        stmtBuscar.setString(2, str);
        stmtBuscar.setString(3, str);
        ResultSet rs = stmtBuscar.executeQuery();
        List<Cliente> listaCliente = new ArrayList();
        while(rs.next()){
            Cliente cliente = new Cliente();
            cliente.setId(rs.getInt("idCliente"));
            cliente.setNome(rs.getString("nome"));
            cliente.setEmail(rs.getString("email"));
            cliente.setTelefone(rs.getString("telefone"));
            cliente.setCelular(rs.getString("celular")); 
            cliente.setDataNasc(rs.getDate("dataNasc"));
            cliente.setCpf(rs.getString("cpf"));
            cliente.setSexo(rs.getString("sexo"));
            cliente.setEndereco(rs.getString("endereco"));
            cliente.setId(rs.getInt("idCliente"));
            listaCliente.add(cliente);
        }
        return listaCliente;
    }
    
    public Cliente buscaId(int id) throws Exception{
        stmtBuscaId = con.prepareStatement("SELECT * FROM cliente WHERE idCliente = ?");
        stmtBuscaId.setInt(1, id);
        ResultSet rs = stmtBuscaId.executeQuery();
        Cliente c = new Cliente();
        if(rs.next()){
            c.setId(rs.getInt("idCliente"));
            c.setNome(rs.getString("nome"));
            c.setCpf(rs.getString("cpf"));
            c.setTelefone(rs.getString("telefone"));
            c.setCelular(rs.getString("celular"));
            c.setEmail(rs.getString("email"));
            c.setEndereco(rs.getString("endereco"));
            c.setDataNasc(rs.getDate("dataNasc"));
            c.setSexo(rs.getString("sexo"));
        }
        return c;
    }
    
    public boolean consultas(int id) throws SQLException{
        stmtConsultas = con.prepareStatement("SELECT * FROM cliente c, consulta co WHERE c.idCliente = co.idCliente AND c.idCliente = ?");
        stmtConsultas.setInt(1, id);
        ResultSet rs = stmtConsultas.executeQuery();
        if(rs.next()){
            return false;
        }else{
            return true;
        }
    }
    
    @Override
    public void finalize() throws Throwable{
        super.finalize();
        stmtInserir.close();
        stmtListar.close();
        stmtDeletar.close();
        stmtAtualizar.close();
        stmtBuscar.close();
        stmtBuscaId.close();
        con.close();
    }
}

